package com.zz.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;

import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.Expression;
import org.springframework.stereotype.Service;

import com.zz.dao.MembershipsDao;
import com.zz.dao.StudentsDao;
import com.zz.entity.Excel;
import com.zz.entity.Memberships;
import com.zz.entity.Students;
import com.zz.entity.StudentsSearch;
import com.zz.service.StudentsService;
@Service
public class StudentsImpl implements StudentsService {
@Autowired
StudentsDao studentsDao;
@Autowired
MembershipsDao membershipsDao;


	@Override
	public List<Students> findAll() {
		return studentsDao.findAll();
	}
	@Override
	public Page<Students> find(StudentsSearch s, Integer page, Integer limit) {
		if(page==null)  page=1;
		if(limit==null)  limit=10;
		Specification<Students> spec=getSpec(s);
		
		Pageable  p=PageRequest.of(page-1, limit);
		
		return studentsDao.findAll(spec, p);
	}
	private Specification<Students> getSpec(StudentsSearch s) {
		return new Specification<Students>() {
			@Override
			public Predicate toPredicate(Root<Students> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
			Predicate p=criteriaBuilder.conjunction();//动态sql表达式
			System.out.println(s);
			List<Expression<Boolean>>list =p.getExpressions();//动态sql表达式集合
			if(!StringUtils.isEmpty(s.getName())) {
				list.add(criteriaBuilder.equal(root.get("name"), s.getName()));
			}
				return p;
			}
		};
	}
	//修改添加   有点迷：
	@Override
	public Integer save(Students s, Memberships m) {
	    s.setMemberships(m);
		if(studentsDao.save(s)!=null) {
			return 1;
		}else {
		    return 0;
		}
	}
	
	//删除
	@Override
	public Integer delete(Integer id) {
		
		try {
			studentsDao.deleteById(id);
			return 1;
		} catch (Exception e) {
			return 0;
		}
	
		
	}
	@Override
	public Integer count() {
		// TODO Auto-generated method stub
		return (int) studentsDao.count();
	}
	@Override

    @Transactional(rollbackOn=Exception.class)
	public void getExcelStudents(MultipartFile file) throws Exception {
		// TODO Auto-generated method stub
		    List<Students> list = new ArrayList<Students>();
	        //1.得到上传的表
	        Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
	        //2、获取sheet1工作表
	        Sheet sheet2 = workbook2.getSheet("sheet1");
	        //获取表的总行数
	        int num = sheet2.getLastRowNum();
	        //总列数
	        int col = sheet2.getRow(0).getLastCellNum();
	        System.out.println("这是学生导入的实现层");
	     	//遍历excel每一行
	        for (int j = 1; j < num; j++) {
	            Row row1 = sheet2.getRow(j);
	            //获取表中第i行，第2列的单元格
	            Cell cell3 = row1.getCell(0);
	            //excel表的第i行，第3列的单元格
	            Cell cell4 = row1.getCell(1);
	            Cell cell5 = row1.getCell(2);
	            Cell cell7 = row1.getCell(3);
	            cell3.setCellType(CellType.STRING); //如果单元格中有数字或者其他格式的数据，则调用setCellType()转换为string类型
	            cell4.setCellType(CellType.STRING);
	            cell5.setCellType(CellType.STRING);
	            cell7.setCellType(CellType.STRING);
	         	//这里new 一个对象，用来装填从页面上传的Excel数据，字段根据上传的excel决定
	            Students e= new Students();
	            e.setCardno(cell3.getStringCellValue());
			    e.setName(cell4.getStringCellValue());
				e.setSex(cell5.getStringCellValue());
				e.setStatus(cell7.getStringCellValue());
				list.add(e);
				 System.out.println(list+","+j+","+num);	
	        } 
	        System.out.println(list+"66666666");
	        studentsDao.saveAll(list);
		
	}
	


	

}
